File manager

File manager - Edit - /home/autoph/public_html/projects/app/Http/Controllers/API/v1/ReportController.php

Back
<?php namespace App\Http\Controllers\API\v1; use App\Http\Controllers\Controller; use App\Models\Applicant; use App\Models\Employee; use App\Models\EmployeeBenefits; use App\Models\EmployeeLeave; use App\Models\EmployeeWfh; use App\Models\Mrf; use App\Models\PersonnelActionForm; use Carbon\Carbon; use DateInterval; use DatePeriod; use DateTime; use Illuminate\Http\Request; use Illuminate\Http\Response; use Illuminate\Support\Facades\Validator; use Illuminate\Support\Facades\DB; class ReportController extends Controller { public function generateReport(Request $request) { // dd($request->all()); // Mapping module numbers to report generation methods $reportGenerators = [ '1' => 'generateLeaveReport', '2' => 'generateOvertimeReport', '3' => 'generateMasterlistReport', '4' => 'generateSalariesReport', '5' => 'generateRolesReport', '6' => 'generateNoScheduleReport', '7' => 'generateAbsentReport', '8' => 'generateTardinessReport', '9' => 'generateBirthdaysReport', '10' => 'generateUndertimeReport', '11' => 'generateDTRReport', '12' => 'generateTravelReport', '13' => 'generateCompanyBenefitsReport', '14' => 'generateLeaveDetailed', '15' => 'generateContributionReport', '16' => 'generateMrfReport', '17' => 'generateApplicantReport', '18' => 'generatePafReport', '19' => 'generateOvertimeCompanyReport', '20' => 'generateLateApprovalReport', 'changeScheduleReport' => 'generatphpeChangeScheduleReport', 'cwsListReport' => 'generateCwsListReport', 'changeRestDay' => 'generateChangeRestDay', 'timeAdjustment' => 'generateTimeAdjustment', ]; $module = $request->modules; if (array_key_exists($module, $reportGenerators)) { $method = $reportGenerators[$module]; return $this->$method($request); } return response()->json(['message' => 'You need to select a valid module']); } public function generateLeaveReport($request) { DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'from_date' => 'required\|date', 'to_date' => 'required\|date', 'leave_id' => 'nullable\|integer', 'status' => 'nullable\|string', ]); // Start the query $query = DB::table('employee_leaves') ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id') ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id'); // Apply filters if ($request->filled('leave_id')) { $query->where('employee_leaves.leave_id', $request->leave_id); } if ($request->filled('status')) { $query->where('employee_leaves.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_leaves.date_from', [$fromDate, $toDate]) ->whereBetween('employee_leaves.date_to', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Dynamically add columns based on request if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_leaves.status = 0 THEN 'Pending / For Recommendation' WHEN employee_leaves.status = 1 THEN 'For Approval' WHEN employee_leaves.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } if (in_array('leave_id', $selectedColumns)) { $transformedColumns[] = 'leave_types.name as leave_id'; } if (in_array('without_pay', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.without_pay = 0 THEN 'No' ELSE 'Yes' END as without_pay"); } if (in_array('half_day', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_leaves.half_day = 0 THEN 'No' ELSE 'Yes' END as half_day"); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = 'employee_leaves.description as description'; } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = 'employee_leaves.created_at as created_at'; } if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_leaves.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_leaves.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = 'employees.employee_id'; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $leaves = $query->get(); // Return the response as JSON return response()->json([ 'data' => $leaves, 'total_hours' => $query->sum('hours'), 'total_days' => $query->sum('days'), ]); } public function generateChangeScheduleReport($request) { DB::enableQueryLog(); $request->validate([ 'start_date' => 'required\|date', 'end_date' => 'required\|date', ]); $query = DB::table('employee_change_schedules') ->join('employees', 'employee_change_schedules.employee_id', '=', 'employees.employee_id') ->join('employees as recommending', 'employee_change_schedules.recommending_id', '=', 'recommending.employee_id') ->join('employees as approving', 'employee_change_schedules.approver_id', '=', 'approving.employee_id') ->select('employee_change_schedules.employee_id', 'employees.firstname', 'employees.lastname', 'employee_change_schedules.status', 'employee_change_schedules.dayoff', 'employee_change_schedules.from_time', 'employee_change_schedules.to_time', 'recommending.firstname as recommending_firstname', 'recommending.lastname as recommending_lastname', 'approving.firstname as approving_firstname', 'approving.lastname as approving_lastname') ->where('employee_change_schedules.status', 2); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } if ($request->company_id) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $query->where('employees.dealer_id', $request->dealer_id); } if ($request->department_id) { $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id'); $query->where('employees.department_id', $request->department_id); } if ($request->filled('date')) { $fromDate = Carbon::parse($request->start_date)->startOfDay(); $toDate = Carbon::parse($request->end_date)->endOfDay(); $query->whereBetween('employee_change_schedules.date', [$fromDate, $toDate]); } return response()->json([ 'data' => $query->get() ]); } public function generateCwsListReport($request) { DB::enableQueryLog(); $request->validate([ 'start_date' => 'required\|date', 'end_date' => 'required\|date', ]); $query = DB::table('employee_schedules') ->join('employees', 'employee_schedules.employee_id', '=', 'employees.employee_id') ->join('schedule_shifts', 'employee_schedules.schedule_id', '=', 'schedule_shifts.id') ->select('schedule_shifts.name', 'schedule_shifts.from_time', 'schedule_shifts.to_time', 'schedule_shifts.day_off', 'schedule_shifts.cws_from_time' , 'schedule_shifts.cws_to_time', 'schedule_shifts.cws_day', 'employees.firstname', 'employees.lastname', 'employees.employee_id', 'schedule_shifts.day_off' , 'schedule_shifts.cws_day', 'employee_schedules.effectivity_date') ->whereNotNull('schedule_shifts.cws_day'); // dd(DB::getQueryLog()); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } if ($request->filled('resign_employee')) { $query->whereNotNull('employees.date_resign'); } else { $query->whereNull('employees.date_resign'); } if ($request->company_id) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $query->where('employees.dealer_id', $request->dealer_id); } if ($request->department_id) { $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id'); $query->where('employees.department_id', $request->department_id); } if ($request->filled('start_date') && $request->filled('end_date')) { $fromDate = Carbon::parse($request->start_date)->startOfDay(); $toDate = Carbon::parse($request->end_date)->endOfDay(); if($request->filled('date') == 1){ $query->whereBetween('employee_schedules.date_from', [$fromDate, $toDate]); } else { $query->whereBetween('employee_schedules.effectivity_date', [$fromDate, $toDate]); } } return response()->json([ 'data' => $query->get() ]); } public function generateChangeRestDay($request) { DB::enableQueryLog(); $request->validate([ 'start_date' => 'required\|date', 'end_date' => 'required\|date', ]); $query = DB::table('employee_change_schedules') ->join('employees', 'employee_change_schedules.employee_id', '=', 'employees.employee_id') ->join('employees as recommending', 'employee_change_schedules.recommending_id', '=', 'recommending.employee_id') ->join('employees as approving', 'employee_change_schedules.approver_id', '=', 'approving.employee_id') ->select('employee_change_schedules.employee_id', 'employees.firstname', 'employees.lastname', 'employee_change_schedules.status', 'employee_change_schedules.dayoff', 'employee_change_schedules.from_time', 'employee_change_schedules.to_time', 'recommending.firstname as recommending_firstname', 'recommending.lastname as recommending_lastname', 'approving.firstname as approving_firstname', 'approving.lastname as approving_lastname', 'employee_change_schedules.date') ->where('employee_change_schedules.dayoff', 1) ->where('employee_change_schedules.status', 2); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } if ($request->filled('resign_employee')) { $query->whereNotNull('employees.date_resign'); } else { $query->whereNull('employees.date_resign'); } if ($request->company_id) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $query->where('employees.dealer_id', $request->dealer_id); } if ($request->department_id) { $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id'); $query->where('employees.department_id', $request->department_id); } if ($request->filled('start_date') && $request->filled('end_date')) { $fromDate = Carbon::parse($request->start_date)->startOfDay(); $toDate = Carbon::parse($request->end_date)->endOfDay(); $query->whereBetween('employee_change_schedules.date', [$fromDate, $toDate]); } return response()->json([ 'data' => $query->get() ]); } public function generateTimeAdjustment($request) { DB::enableQueryLog(); $request->validate([ 'start_date' => 'required\|date', 'end_date' => 'required\|date', ]); $query = DB::table('employee_time_adjustments') ->join('employees', 'employee_time_adjustments.employee_id', '=', 'employees.employee_id') ->join('employees as recommending', 'employee_time_adjustments.recommending_id', '=', 'recommending.employee_id') ->join('employees as approving', 'employee_time_adjustments.approver_id', '=', 'approving.employee_id') ->select('employee_time_adjustments.employee_id', 'employees.firstname', 'employees.lastname', 'employee_time_adjustments.status', 'employee_time_adjustments.in_out', 'employee_time_adjustments.time', 'recommending.firstname as recommending_firstname', 'recommending.lastname as recommending_lastname', 'approving.firstname as approving_firstname', 'approving.lastname as approving_lastname', 'employee_time_adjustments.date'); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } if ($request->company_id) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $query->where('employees.dealer_id', $request->dealer_id); } if ($request->filled('start_date') && $request->filled('end_date')) { $fromDate = Carbon::parse($request->start_date)->startOfDay(); $toDate = Carbon::parse($request->end_date)->endOfDay(); $query->whereBetween('employee_time_adjustments.date', [$fromDate, $toDate]); } return response()->json([ 'data' => $query->get() ]); } public function generateOvertimeReport($request) { // dd($request->all()); // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'from_date' => 'required\|date', 'to_date' => 'required\|date', 'charge_to' => 'nullable\|integer', 'status' => 'nullable\|string', ]); // Start the query $query = DB::table('employee_overtimes') ->join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id') ->leftjoin('companies', 'employee_overtimes.charge_to', '=', 'companies.id'); // Apply filters if ($request->filled('charge_to')) { $query->where('employee_overtimes.charge_to', $request->charge_to); } if ($request->filled('status')) { $query->where('employee_overtimes.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_overtimes.date_from', [$fromDate, $toDate]); $query->whereBetween('employee_overtimes.date_to', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Conditionally include status if 'status' is in $columns if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_overtimes.status = 0 THEN 'Pending / For Recommendation' WHEN employee_overtimes.status = 1 THEN 'For Approval' WHEN employee_overtimes.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } // Handle transformed columns dynamically if (in_array('charge_to', $selectedColumns)) { $transformedColumns[] = 'companies.name as charge_to'; } if (in_array('meal', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.meal = 0 THEN 'No' ELSE 'Yes' END as meal"); } if (in_array('transportation', $selectedColumns)) { $transformedColumns[] = DB::raw("CASE WHEN employee_overtimes.transportation = 0 THEN 'No' ELSE 'Yes' END as transportation"); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_overtimes.description as description'); } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_overtimes.created_at as created_at'); } // Conditionally join employees for recommending_id if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_overtimes.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } // Conditionally join employees for approver_id if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_overtimes.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $overtime = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $overtime, 'total_hours' => $query->sum('hours'), // This line can also be used if not grouping ]); } public function generateMasterlistReport($request) { DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'date_hired_from' => 'required\|date', 'date_hired_to' => 'required\|date' ]); $query = DB::table('employees') ->join('personal_information', 'personal_information.employee_id', '=', 'employees.employee_id', 'left') ->whereNull('employees.date_resign'); if ($request->filled('date_hired_from') && $request->filled('date_hired_to')) { $fromDate = Carbon::parse($request->date_hired_from)->startOfDay(); $toDate = Carbon::parse($request->date_hired_to)->endOfDay(); $query->whereBetween('employees.date_hired', [$fromDate, $toDate]); } $personalInformationColumns = ['age', 'gender', 'religion', 'tin', 'philhealth', 'citizenship', 'sss', 'pagibig', 'civil_status', 'hair_color', 'blood_type', 'eye_color', 'height', 'weight']; $selectedColumns = array_map(function($column) use ($personalInformationColumns) { if (in_array($column, $personalInformationColumns)) { return 'personal_information.' . $column; } return 'employees.' . $column; }, $request->columns); $transformedColumns = []; if (in_array('employees.company_id', $selectedColumns)) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $transformedColumns[] = DB::raw("companies.name as company_id"); if ($request->company_id) { $query->where('employees.company_id', $request->company_id); } } if (in_array('employees.position_id', $selectedColumns)) { $query->leftJoin('job_titles', 'job_titles.id', '=', 'employees.position_id'); $transformedColumns[] = DB::raw("job_titles.name as position_id"); } if (in_array('employees.dealer_id', $selectedColumns)) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $transformedColumns[] = DB::raw("dealerships.name as dealer_id"); if ($request->dealer_id) { $query->where('employees.companydealer_id_id', $request->dealer_id); } } if (in_array('employees.classification_id', $selectedColumns)) { $query->leftJoin('job_classifications', 'job_classifications.id', '=', 'employees.classification_id'); $transformedColumns[] = DB::raw("job_classifications.name as classification_id"); } if (in_array('employees.employment_status_id', $selectedColumns)) { $query->leftJoin('employment_types', 'employment_types.id', '=', 'employees.employment_status_id'); $transformedColumns[] = DB::raw("employment_types.name as employment_status_id"); } if (in_array('employees.group_id', $selectedColumns)) { $query->leftJoin('groups', 'groups.id', '=', 'employees.group_id'); $transformedColumns[] = DB::raw("groups.name as group_id"); } if (in_array('employees.division_id', $selectedColumns)) { $query->leftJoin('divisions', 'divisions.id', '=', 'employees.division_id'); $transformedColumns[] = DB::raw("divisions.name as division_id"); } if (in_array('employees.department_id', $selectedColumns)) { $query->leftJoin('departments', 'departments.id', '=', 'employees.department_id'); $transformedColumns[] = DB::raw("departments.name as department_id"); } if (in_array('employees.section_id', $selectedColumns)) { $query->leftJoin('sections', 'sections.id', '=', 'employees.section_id'); $transformedColumns[] = DB::raw("sections.name as section_id"); } if (in_array('employees.office_id', $selectedColumns)) { $query->leftJoin('offices', 'offices.id', '=', 'employees.office_id'); $transformedColumns[] = DB::raw("offices.name as office_id"); } if (in_array('employees.unit_id', $selectedColumns)) { $query->leftJoin('units', 'units.id', '=', 'employees.unit_id'); $transformedColumns[] = DB::raw("units.name as unit_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $masterlist = $query->get(); // dd($transformedColumns); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $masterlist, ]); } public function generateSalariesReport($request) { // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'salary_effectivity_date' => 'required\|date', ]); // Start the query $query = DB::table('salary_and_wages') ->join('employees', 'salary_and_wages.employee_id', '=', 'employees.employee_id'); if ($request->filled('salary_effectivity_date')) { $fromDate = Carbon::parse($request->salary_effectivity_date)->startOfDay(); $query->where('salary_and_wages.salary_effectivity_date', '>=', $fromDate); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Handle transformed columns dynamically if ($request->filled('payroll_rate')) { $query->where('salary_and_wages.payroll_rate', $request->payroll_rate); } if ($request->filled('salary_rate')) { $query->whereBetween('salary_and_wages.salary_rate', [$request->salary_rate['min'], $request->salary_rate['max']]); } if ($request->filled('payment_type')) { $query->where('salary_and_wages.payment_type', $request->payment_type); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $salaries = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $salaries ]); } public function generateRolesReport(Request $request) { // Enable query logging for debugging DB::enableQueryLog(); // Validate the request $request->validate([ 'columns' => 'required\|array', ]); // Start building the query $query = DB::table('employees') ->join('users', 'employees.employee_id', '=', 'users.employee_id') ->join('user_roles', 'users.id', '=', 'user_roles.user_id') ->join('roles', 'user_roles.role_id', '=', 'roles.id') ->join('role_permissions', 'roles.id', '=', 'role_permissions.role_id') ->join('permissions', 'role_permissions.permission_id', '=', 'permissions.id') ->join('modules', 'role_permissions.module_id', '=', 'modules.id') ->join('menus', 'modules.menu_id', '=', 'menus.id') ->where('modules.system_id', 3) ->where('menus.enabled', 1) ->where('users.employee_id', '!=', 'sadmin') ->groupBy('roles.id', 'roles.name', 'modules.id', 'modules.name', 'menus.system_id', 'employees.firstname', 'employees.lastname', 'employees.employee_id'); if ($request->company_id) { $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->where('employees.dealer_id', $request->dealer_id); } // Selected columns from the request $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; if (in_array('roles_name', $selectedColumns)) { $transformedColumns[] = DB::raw("roles.name as roles_name"); } if (in_array('menu_name', $selectedColumns)) { $transformedColumns[] = DB::raw("menus.name as menu_name"); } if (in_array('menu_description', $selectedColumns)) { $transformedColumns[] = DB::raw("modules.description as menu_description"); } // Always include these columns $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Add the GROUP_CONCAT for permissions $transformedColumns[] = DB::raw("GROUP_CONCAT(DISTINCT permissions.name ORDER BY permissions.id SEPARATOR ', ') as menu_permission"); // Filter out unwanted columns $excludedColumns = ['description', 'created_at']; $selectedColumns = array_diff($selectedColumns, $excludedColumns); // Add transformed columns to the query $query->select(array_merge($transformedColumns)); $query->orderBy('employees.lastname', 'ASC'); $query->orderBy('employees.employee_id', 'ASC'); // Execute the query and get results $roles = $query->get(); // Optional: Uncomment this to view the executed SQL query // dd(DB::getQueryLog()); // Return the results as JSON return response()->json([ 'data' => $roles, ]); } public function generateNoScheduleReport($request) { DB::enableQueryLog(); $query = DB::table('employees') ->leftJoin('employee_schedules', 'employees.employee_id', '=', 'employee_schedules.employee_id') ->where('employee_schedules.employee_id', NULL) ->where('employees.enabled', 1); $transformedColumns = []; if ($request->company_id) { $query->leftJoin('companies', 'companies.id', '=', 'employees.company_id'); $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->leftJoin('dealerships', 'dealerships.id', '=', 'employees.dealer_id'); $query->where('employees.dealer_id', $request->dealer_id); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; $query->select($transformedColumns); $noSchedule = $query->get(); // Return the response as JSON return response()->json([ 'data' => $noSchedule ]); } public function generateAbsentReport($request) { set_time_limit(300); // Prevents timeout DB::enableQueryLog(); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { // Fetch employees individually $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->whereNull('date_resign') ->get(); } else { $employeeList = Employee::where('enabled', 1) ->whereNull('date_resign'); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $employeeAttendance = new EmployeeAttendanceController(); $start_date = new DateTime($request->start_date); $end_date = new DateTime($request->end_date); $end_date->modify('+1 day'); $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date); $data = []; foreach ($employees as $employee) { $employeeData = []; foreach ($period as $date) { $formattedDate = $date->format('Y-m-d'); $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->time_keeping_id); $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id); $holiday = $employeeAttendance->getHoliday($formattedDate, $employee, $schedule); $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id); $absent = $employeeAttendance->addAbsenceData($employeeData, $timelogs, $leave, $holiday, $schedule, $formattedDate); if ($absent) { $employeeData[] = $formattedDate; } } $data[] = [ 'employee_id' => $employee->employee_id, 'firstname' => $employee->firstname, 'lastname' => $employee->lastname, 'absent' => $employeeData, ]; } return response()->json([ 'data' => $data ]); } public function generateTardinessReport($request) { DB::enableQueryLog(); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { // Fetch employees individually $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } else { $employeeList = Employee::where('enabled', 1); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $employeeAttendance = new EmployeeAttendanceController(); $start_date = new DateTime($request->start_date); $end_date = new DateTime($request->end_date); $end_date->modify('+1 day'); $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date); $data = []; foreach ($employees as $employee) { $employeeData = []; foreach ($period as $date) { $formattedDate = $date->format('Y-m-d'); $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id); $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id); $halfDayLeave = $leave && $leave->half_day == 1; $ob = $employeeAttendance->getOB($formattedDate, $employee->employee_id); $offsetAvailment = $employeeAttendance->getOffsetAvailment($formattedDate, $employee->employee_id); $tardiness = $employeeAttendance->getTardiness($timelogs, $schedule, $halfDayLeave, $ob, $offsetAvailment, $employee->classification_id); if ($tardiness) { $employeeData[] = $formattedDate.'\|'.$tardiness; } } $data[] = [ 'employee_id' => $employee->employee_id, 'firstname' => $employee->firstname, 'lastname' => $employee->lastname, 'tardiness' => $employeeData, ]; } return response()->json([ 'data' => $data ]); } public function generateBirthdaysReport($request) { $employeeList = Employee::where('employees.enabled', 1) ->join('personal_information', 'employees.employee_id', '=', 'personal_information.employee_id'); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } $employees = $employeeList->select('employees.employee_id', 'firstname', 'lastname', 'birth_date') ->whereRaw('MONTH(birth_date) IN (' . implode(',', $request->month) . ')') ->get(); return response()->json([ 'data' => $employees ]); } public function generateMrfReport($request) { $mrfList = Mrf::with([ 'company', 'jobtitle', 'employmenttype', 'requestor', 'recommendor', 'endorser', 'approver', 'employee', 'nature', 'job_classification' ])->where('enabled', 1); if ($request->status_id) { $mrfList->where('status', $request->status_id); } if ($request->company_id) { $mrfList->where('company_id', $request->company_id); } if ($request->job_title_id) { $mrfList->where('job_title', $request->job_title_id); } if ($request->job_classification_id) { $mrfList->where('classification', $request->job_classification_id); } if ($request->type_id) { $mrfList->where('nature_of_request', $request->type_id); } if ($request->filled('start_date') && $request->filled('end_date')) { $fromDate = Carbon::parse($request->start_date)->startOfDay(); $toDate = Carbon::parse($request->end_date)->endOfDay(); $mrfList->whereBetween('mrf.created_at', [$fromDate, $toDate]); } $mrfList = $mrfList->get(); return response()->json([ 'data' => $mrfList ]); } public function generatePafReport($request) { DB::enableQueryLog(); $data = PersonnelActionForm::with([ 'employee', 'type_of_movement', 'requestor', 'recommendor', 'endorser', 'approver', 'unit', ])->select('personnel_action_forms.id', 'jt_prev.name as prev_position_name', 'jt_next.name as next_position_name', 'personnel_action_forms.*') ->join('job_titles as jt_prev', 'personnel_action_forms.position_id', '=', 'jt_prev.id') ->join('job_titles as jt_next', 'personnel_action_forms.position' , '=', 'jt_next.id'); if ($request->employee_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('employee_id', $request->employee_id); }); } if ($request->status_id) { $data->where('status', $request->status_id); } if ($request->company_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('company_id', $request->company_id); }); $data->where('company_id', $request->company_id); } if ($request->company_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('classification_id', $request->job_classification_id); }); } if ($request->dealer_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('dealer_id', $request->dealer_id); }); } if ($request->division_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('division_id', $request->division_id); }); } if ($request->department_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('department_id', $request->department_id); }); } if ($request->section_id) { $data->whereHas('employee', function ($query) use ($request) { $query->where('section_id', $request->section_id); }); } if ($request->filled('start_date') && $request->filled('end_date')) { $fromDate = Carbon::parse($request->start_date)->startOfDay(); $toDate = Carbon::parse($request->end_date)->endOfDay(); $data->whereBetween('personnel_action_forms.created_at', [$fromDate, $toDate]); } // dd(DB::getQueryLog()); $mrfList = $data->get(); return response()->json([ 'data' => $mrfList ]); } public function generateApplicantReport($request) { // dd($request->priceRange[0]); DB::enableQueryLog(); $mrfList = Applicant::with([ 'mrf' ])->where('enabled', 1); if ($request->status_id) { $mrfList->where('status', $request->status_id); } if ($request->job_title_id) { $mrfList->whereHas('mrf', function ($query) use ($request) { $query->where('job_title', $request->job_title_id); }); } $mrfList->whereBetween('last_salary', [$request->priceRange[0], $request->priceRange[1]]); $mrfList = $mrfList->get(); // dd(DB::getQueryLog()); return response()->json([ 'data' => $mrfList ]); } public function generateUndertimeReport($request) { DB::enableQueryLog(); $employees = []; if ($request->filled('employee_id') && is_array($request->employee_id) && !empty($request->employee_id)) { $employees = Employee::select('employee_id', 'firstname', 'lastname') ->whereIn('employee_id', $request->employee_id) ->get(); } else { $employeeList = Employee::where('enabled', 1); if ($request->company_id) { $employeeList->where('company_id', $request->company_id); } if ($request->dealer_id) { $employeeList->where('dealer_id', $request->dealer_id); } // Get all employees that match the criteria $employees = $employeeList->select('employee_id', 'firstname', 'lastname')->get(); } $employeeAttendance = new EmployeeAttendanceController(); $start_date = new DateTime($request->start_date); $end_date = new DateTime($request->end_date); $end_date->modify('+1 day'); $period = new DatePeriod($start_date, new DateInterval('P1D'), $end_date); $data = []; foreach ($employees as $employee) { $employeeData = []; foreach ($period as $date) { $formattedDate = $date->format('Y-m-d'); $timelogs = $employeeAttendance->getTimelog($formattedDate, $employee->employee_id); $schedule = $employeeAttendance->getSchedule($formattedDate, $employee->employee_id); $ob = $employeeAttendance->getOB($formattedDate, $employee->employee_id); $workSuspension = $employeeAttendance->getWorkSuspension($formattedDate); $wfhRecord = EmployeeWfh::whereNotNull('attachment')->where(['employee_id'=> $employee->employee_id, 'date'=>$formattedDate, 'status'=>2, 'validated'=>1])->first(); $leave = $employeeAttendance->getLeave($formattedDate, $employee->employee_id); $offsetAvailment = $employeeAttendance->getOffsetAvailment($formattedDate, $employee->employee_id); $halfDayLeave = $leave && $leave->half_day == 1; $undertime = $employeeAttendance->getUndertime($timelogs, $schedule, $workSuspension, $formattedDate, $ob, $offsetAvailment, $wfhRecord, $halfDayLeave); if ($undertime) { $employeeData[] = $formattedDate.'\|'.$undertime; } } $data[] = [ 'employee_id' => $employee->employee_id, 'firstname' => $employee->firstname, 'lastname' => $employee->lastname, 'undertime' => $employeeData, ]; } return response()->json([ 'data' => $data ]); } public function generateDTRReport($request) { $end_date = date('Y-m-d', strtotime($request->end_date . '+1 day')); $employeeQuery = Employee::select('employee_id', 'firstname', 'lastname', 'company_id', 'dealer_id') ->with([ 'timelog' => function ($query) use ($request, $end_date) { $query->whereBetween('datetimelog', [$request->start_date, $end_date]); }, 'company:id,name', 'dealership:id,name', ]) ->where('enabled', 1); if ($request->filled('employee_id') && is_array($request->employee_id)) { $employeeQuery->whereIn('employee_id', $request->employee_id); } else { if ($request->filled('company_id')) { $employeeQuery->where('company_id', $request->company_id); } if ($request->filled('dealer_id')) { $employeeQuery->where('dealer_id', $request->dealer_id); } } $results = []; $employeeQuery->chunk(100, function ($employees) use (&$results) { foreach ($employees as $employee) { $results[] = $employee; } }); return response()->json([ 'data' => $results, ]); } public function generateTravelReport($request) { DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'from_date' => 'required\|date', 'to_date' => 'required\|date', 'status' => 'nullable\|string', ]); // Start the query $query = DB::table('employee_obs') ->join('employees', 'employee_obs.employee_id', '=', 'employees.employee_id'); if ($request->company_id) { $query->where('employees.company_id', $request->company_id); } if ($request->dealer_id) { $query->where('employees.dealer_id', $request->dealer_id); } if ($request->filled('status')) { $query->where('employee_obs.status', $request->status); } if ($request->filled('from_date') && $request->filled('to_date')) { $fromDate = Carbon::parse($request->from_date)->startOfDay(); $toDate = Carbon::parse($request->to_date)->endOfDay(); $query->whereBetween('employee_obs.date_from', [$fromDate, $toDate]); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Conditionally include status if 'status' is in $columns if (in_array('status', $selectedColumns)) { $transformedColumns[] = DB::raw(" CASE WHEN employee_obs.status = 0 THEN 'Pending / For Recommendation' WHEN employee_obs.status = 1 THEN 'For Approval' WHEN employee_obs.status = 2 THEN 'Approved' ELSE 'Denied' END as status "); } if (in_array('date_from', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.date_from as date_from'); } if (in_array('date_to', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.date_to as date_to'); } if (in_array('description', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.description as description'); } if (in_array('created_at', $selectedColumns)) { $transformedColumns[] = DB::raw('employee_obs.created_at as created_at'); } // Conditionally join employees for recommending_id if (in_array('recommending_id', $selectedColumns)) { $query->leftJoin('employees as recommending_employee', 'employee_obs.recommending_id', '=', 'recommending_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(recommending_employee.firstname, ' ', recommending_employee.lastname) as recommending_id"); } // Conditionally join employees for approver_id if (in_array('approver_id', $selectedColumns)) { $query->leftJoin('employees as approver_employee', 'employee_obs.approver_id', '=', 'approver_employee.employee_id'); $transformedColumns[] = DB::raw("CONCAT(approver_employee.firstname, ' ', approver_employee.lastname) as approver_id"); } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'date_from'; }); $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'date_to'; }); $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $ob = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $ob, ]); } public function generateCompanyBenefitsReport($request) { $data = []; $employeeCountPerCompany = DB::table('employees as e') ->join('companies as c', 'e.company_id', '=', 'c.id') ->select('c.id', 'c.name', 'c.code', DB::raw('COUNT(e.company_id) as employee_count_per_company')) ->where('e.enabled', 1) ->groupBy('c.name', 'e.company_id') ->get(); foreach($employeeCountPerCompany as $company){ $data[$company->id]['company_id'] = $request->id; $data[$company->id]['company_name'] = $company->name; $data[$company->id]['company_code'] = $company->code; $data[$company->id]['employee_count'] = $company->employee_count_per_company; $employeeQuery = Employee::select('employee_id')->where('company_id', $request->id); $employee_ids = $employeeQuery->pluck('employee_id'); $vacation_counts = EmployeeLeave::where('leave_id', 2) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_vl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_vl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR VACATION LEAVE $data[$company->id]['total_vl_wp'] = $vacation_counts->total_vl_wp; $data[$company->id]['total_vl_wop'] = $vacation_counts->total_vl_wop; $sick_counts = EmployeeLeave::where('leave_id', 3) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_sl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_sl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR SICK LEAVE $data[$company->id]['total_sl_wp'] = $sick_counts->total_sl_wp; $data[$company->id]['total_sl_wop'] = $sick_counts->total_sl_wop; // TOTAL BEREAVEMENT LEAVE $data[$company->id]['total_bl_wop'] = 0; $total_amount_of_benefits = DB::table('employees as e') ->join('employee_benefits as eb', 'e.employee_id', '=', 'eb.employee_id') ->selectRaw(' SUM(CASE WHEN benefits_type_id = 3 THEN eb.amount ELSE 0 END) as meal_total, SUM(CASE WHEN benefits_type_id = 2 THEN eb.amount ELSE 0 END) as transpo_total, SUM(CASE WHEN benefits_type_id = 5 THEN eb.amount ELSE 0 END) as transpo_subsidy_total, SUM(CASE WHEN benefits_type_id = 17 THEN eb.amount ELSE 0 END) as transpo_allowance_total, SUM(CASE WHEN benefits_type_id = 18 THEN eb.amount ELSE 0 END) as company_car_total, SUM(CASE WHEN benefits_type_id = 19 THEN eb.amount ELSE 0 END) as gas_allowance_total, SUM(CASE WHEN benefits_type_id = 20 THEN eb.amount ELSE 0 END) as parking_total, SUM(CASE WHEN benefits_type_id = 21 THEN eb.amount ELSE 0 END) as company_uniform_total, SUM(CASE WHEN benefits_type_id = 22 THEN eb.amount ELSE 0 END) as sm_uniform_total, SUM(CASE WHEN benefits_type_id = 23 THEN eb.amount ELSE 0 END) as hmo_total, SUM(CASE WHEN benefits_type_id = 24 THEN eb.amount ELSE 0 END) as first_aid_total, SUM(CASE WHEN benefits_type_id = 25 THEN eb.amount ELSE 0 END) as ligpai_total, SUM(CASE WHEN benefits_type_id = 26 THEN eb.amount ELSE 0 END) as calamity_assistance_total, SUM(CASE WHEN benefits_type_id = 27 THEN eb.amount ELSE 0 END) as bereavement_assistance_total, SUM(CASE WHEN benefits_type_id = 28 THEN eb.amount ELSE 0 END) as intereset_fee_total, SUM(CASE WHEN benefits_type_id = 29 THEN eb.amount ELSE 0 END) as laptop_prog_total, SUM(CASE WHEN benefits_type_id = 30 THEN eb.amount ELSE 0 END) as company_outing_total, SUM(CASE WHEN benefits_type_id = 31 THEN eb.amount ELSE 0 END) as com_allowance_total, SUM(CASE WHEN benefits_type_id = 32 THEN eb.amount ELSE 0 END) as stay_in_total, SUM(CASE WHEN benefits_type_id = 33 THEN eb.amount ELSE 0 END) as shuttle_service_total ') ->where('e.enabled', 1) ->where('e.company_id', $request->id) ->whereMonth('eb.date_from', $request->month) ->first(); foreach($total_amount_of_benefits as $key => $amount_of_benefits){ $data[$company->id][$key] = $amount_of_benefits; } } return response()->json([ 'data' => array_values($data) ]); } public function generateLeaveDetailed($request) { $data = []; $employeeCountPerCompany = DB::table('employees as e') ->join('companies as c', 'e.company_id', '=', 'c.id') ->select('c.id', 'c.name', 'c.code', DB::raw('COUNT(e.company_id) as employee_count_per_company')) ->where('e.enabled', 1) ->groupBy('c.name', 'e.company_id') ->get(); foreach($employeeCountPerCompany as $company){ $data[$company->id]['company_id'] = $request->id; $data[$company->id]['company_name'] = $company->name; $data[$company->id]['company_code'] = $company->code; $data[$company->id]['employee_count'] = $company->employee_count_per_company; $employeeQuery = Employee::select('employee_id')->where('company_id', $request->id); $employee_ids = $employeeQuery->pluck('employee_id'); $vacation_counts = EmployeeLeave::where('leave_id', 2) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_vl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_vl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR VACATION LEAVE $data[$company->id]['total_vl_wp'] = $vacation_counts->total_vl_wp; $data[$company->id]['total_vl_wop'] = $vacation_counts->total_vl_wop; $sick_counts = EmployeeLeave::where('leave_id', 3) ->whereIn('employee_id', $employee_ids) ->selectRaw(' SUM(CASE WHEN without_pay = 0 THEN days ELSE 0 END) as total_sl_wp, SUM(CASE WHEN without_pay = 1 THEN days ELSE 0 END) as total_sl_wop ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR SICK LEAVE $data[$company->id]['total_sl_wp'] = $sick_counts->total_sl_wp; $data[$company->id]['total_sl_wop'] = $sick_counts->total_sl_wop; // TOTAL BEREAVEMENT LEAVE $data[$company->id]['total_bl_wop'] = 0; $vacation_reason_counts = EmployeeLeave::whereIn('employee_id', $employee_ids) ->selectRaw(' COUNT(CASE WHEN leave_reason_id = 1 THEN 1 ELSE NULL END) as total_personal_vl, COUNT(CASE WHEN leave_reason_id = 2 THEN 1 ELSE NULL END) as total_fr_vl, COUNT(CASE WHEN leave_reason_id = 3 THEN 1 ELSE NULL END) as total_rl_vl, COUNT(CASE WHEN leave_reason_id = 4 THEN 1 ELSE NULL END) as total_emergency_vl, COUNT(CASE WHEN leave_reason_id = 5 THEN 1 ELSE NULL END) as total_others_vl, COUNT(CASE WHEN leave_reason_id = 6 THEN 1 ELSE NULL END) as total_cd_sl, COUNT(CASE WHEN leave_reason_id = 7 THEN 1 ELSE NULL END) as total_msp_sl, COUNT(CASE WHEN leave_reason_id = 8 THEN 1 ELSE NULL END) as total_rd_sl, COUNT(CASE WHEN leave_reason_id = 9 THEN 1 ELSE NULL END) as total_eent_sl, COUNT(CASE WHEN leave_reason_id = 10 THEN 1 ELSE NULL END) as total_idi_sl, COUNT(CASE WHEN leave_reason_id = 11 THEN 1 ELSE NULL END) as total_hf_sl, COUNT(CASE WHEN leave_reason_id = 12 THEN 1 ELSE NULL END) as total_or_sl, COUNT(CASE WHEN leave_reason_id = 13 THEN 1 ELSE NULL END) as total_dr_sl, COUNT(CASE WHEN leave_reason_id = 14 THEN 1 ELSE NULL END) as total_others_sl ') ->whereRaw('MONTH(date_from) IN (' . implode(',', $request->month) . ')') ->where('status', 2) ->first(); // TOTAL FOR VACATION LEAVE REASON $data[$company->id]['total_personal_vl'] = $vacation_reason_counts->total_personal_vl; $data[$company->id]['total_fr_vl'] = $vacation_reason_counts->total_fr_vl; $data[$company->id]['total_rl_vl'] = $vacation_reason_counts->total_rl_vl; $data[$company->id]['total_emergency_vl'] = $vacation_reason_counts->total_emergency_vl; $data[$company->id]['total_others_vl'] = $vacation_reason_counts->total_others_vl; $data[$company->id]['total_cd_sl'] = $vacation_reason_counts->total_cd_sl; $data[$company->id]['total_msp_sl'] = $vacation_reason_counts->total_msp_sl; $data[$company->id]['total_rd_sl'] = $vacation_reason_counts->total_rd_sl; $data[$company->id]['total_eent_sl'] = $vacation_reason_counts->total_eent_sl; $data[$company->id]['total_idi_sl'] = $vacation_reason_counts->total_idi_sl; $data[$company->id]['total_hf_sl'] = $vacation_reason_counts->total_hf_sl; $data[$company->id]['total_or_sl'] = $vacation_reason_counts->total_or_sl; $data[$company->id]['total_dr_sl'] = $vacation_reason_counts->total_dr_sl; $data[$company->id]['total_others_sl'] = $vacation_reason_counts->total_others_sl; } return response()->json([ 'data' => array_values($data) ]); } public function generateContributionReport($request) { // Validate the request DB::enableQueryLog(); $request->validate([ 'columns' => 'required\|array', 'date_from' => 'required\|date', 'date_to' => 'required\|date', ]); // Start the query $query = DB::table('payroll_summaries') ->join('employees', 'payroll_summaries.employee_id', '=', 'employees.employee_id'); if ($request->filled('date_from')) { $fromDate = Carbon::parse($request->date_from)->startOfDay(); $toDate = Carbon::parse($request->date_to)->startOfDay(); $query->where('payroll_summaries.payroll_date', '>=', $fromDate)->where('payroll_summaries.payroll_date', '<=', $toDate); } if ($request->company_id) { $query->where('payroll_summaries.company_id', $request->company_id); } // Set the selected columns $selectedColumns = $request->columns; // Prepare transformed columns $transformedColumns = []; // Handle transformed columns dynamically // if ($request->filled('payroll_rate')) { // $query->where('salary_and_wages.payroll_rate', $request->payroll_rate); // } // if ($request->filled('salary_rate')) { // $query->whereBetween('salary_and_wages.salary_rate', [$request->salary_rate['min'], $request->salary_rate['max']]); // } // if ($request->filled('payment_type')) { // $query->where('salary_and_wages.payment_type', $request->payment_type); // } $transformedColumns[] = DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as fullname"); $transformedColumns[] = "employees.employee_id"; // Remove 'description' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'description'; }); // Remove 'created_at' from selectedColumns if present $selectedColumns = array_filter($selectedColumns, function ($column) { return $column !== 'created_at'; }); // Merge selected columns with transformed columns $finalColumns = array_merge($selectedColumns, $transformedColumns); // Select the final columns $query->select($finalColumns); // Fetch the results $salaries = $query->get(); // dd(DB::getQueryLog()); // Return the response as JSON return response()->json([ 'data' => $salaries ]); } private function generateOvertimeCompanyReport($request){ // dd($request->all()); $departments = DB::table('departments') ->when($request->department_id, fn($query) => $query->where('id', $request->department_id) ) ->get(); $result =[]; foreach($departments as $department){ DB::enableQueryLog(); $data = \App\Models\ProcessedOvertime:: join('payroll_summaries as ps', fn($join) => $join->on( 'ps.employee_id','=','processed_overtimes.employee_id' )) ->join('payrolls','ps.payroll_date','=','payrolls.payroll_date') ->join('employees','processed_overtimes.employee_id','=','employees.employee_id') ->leftJoin('departments','processed_overtimes.department_id','=','departments.id') ->where('processed_overtimes.department_id',$department->id); // whereBetween('date',[$validated['from_date'], $validated['to_date']]) if($request->payroll_date){ $data = $data->whereIn('processed_overtimes.payroll_date',$request->payroll_date); } if($request->selectedCompanyId > 0 ){ $data = $data->where('processed_overtimes.company_id',$request->selectedCompanyId); } if($request->dealership_id > 0){ $data = $data->where('processed_overtimes.dealership_id',$request->dealership_id); } $data = $data->select( DB::raw("CONCAT(employees.firstname,' ',employees.lastname) as name"), // 'processed_overtimes.employee_id', DB::raw('departments.name as departments'), DB::raw('SUM(ps.overtime_total) as total_ot_pay'), DB::raw('SUM(ps.holiday_total) as holiday_total'), DB::raw('SUM(ps.restday_total) as dayOff_pay'), DB::raw('SUM(ps.night_diff_total) as night_diff_pay'), DB::raw('SUM(processed_overtimes.no_of_hours) as total_over_times'), DB::raw('COUNT(DISTINCT processed_overtimes.date) as frequency'), DB::raw('SUM(processed_overtimes.meal_amount) as total_meal'), DB::raw('SUM(processed_overtimes.transportation) as total_transpo'), DB::raw( '( SUM(ps.holiday_total) + SUM(ps.restday_total) + SUM(ps.night_diff_total) + SUM(processed_overtimes.meal_amount) + SUM(processed_overtimes.transportation) ) as row_total' ) ) ->groupBy( // 'employee_id', 'departments.name', DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)") ) ->get(); // dd(DB::getQueryLog()); if($data->isNotEmpty()){ $result[$department->name] = $data; } } return response()->json(['data'=> $result]); } private function getPreviousCutOff($company_id,$payroll_date) { // var_dump($payroll_date); $salary_date = Carbon::parse($payroll_date)->format('j'); // var_dump($salary_date); $cut_off = \App\Models\CutoffSetting::where(fn($q) => $q->where('company_id', $company_id)->orWhereNull('company_id')) ->where('salary_day','!=', $salary_date) ->first(); // echo'<pre>';print_r($cut_off);echo'<pre>'; $start_month = Carbon::parse($payroll_date)->format('Y-m'); if($salary_date < $cut_off->salary_day){ $start_month = Carbon::parse($start_month . '-01')->subMonth()->format('Y-m'); } // var_dump('start month '.$start_month); // Adjust the logic based on the cutoff days if ($cut_off->day_start > $cut_off->day_end) { // When day_start is greater than day_end, start_month should be the previous month $start_month = Carbon::parse($start_month . '-01')->subMonth()->format('Y-m'); $end_month = Carbon::parse($start_month . '-01')->addMonth()->format('Y-m'); } else { // Otherwise, use the current month for both start and end $end_month = $start_month; } // Construct and format day_start and day_end with Y-m-d $day_start_date = Carbon::parse($start_month . '-' . $cut_off->day_start); $day_end_date = Carbon::parse($end_month . '-' . $cut_off->day_end); // dd(['start_month'=> $start_month,'end_month'=> $end_month,'day_start' => $day_start_date,'day_end' => $day_end_date]); // Generate array of dates from start to end $date_range = []; $current_date = $day_start_date->copy(); while ($current_date->lte($day_end_date)) { $date_range[] = $current_date->format('Y-m-d'); $current_date->addDay(); // Increment by 1 day } return [ 'cut_off' => $cut_off->cut_off, 'start_date' => $day_start_date->format('Y-m-d'), 'end_date' => $day_end_date->format('Y-m-d'), 'date_range' => $date_range ]; } private function generateLateApprovalReport(Request $request) { $cut_off =''; $cut_off = $this->getPreviousCutOff($request->selectedCompanyId,$request->payroll_date); // echo'<pre>';print_r( $cut_off['date_range']);echo'</pre>'; if ($request->type == 1) { $data = \App\Models\EmployeeLeave:: join('payroll_summaries as ps', fn($join) => $join->on( 'ps.employee_id', '=', 'employee_leaves.employee_id' )) ->join('employees', 'employee_leaves.employee_id', '=', 'employees.employee_id') ->join('employees as approver', 'employee_leaves.approver_id', '=', 'approver.employee_id') ->join('leave_types', 'employee_leaves.leave_id', '=', 'leave_types.id') ->join('leave_reasons', 'employee_leaves.leave_reason_id', '=', 'leave_reasons.id') ->join('companies','employee_leaves.company_id','companies.id') ->join('dealerships','employee_leaves.dealer_id','dealerships.id') ->where('employee_leaves.posted',0) ->where('employee_leaves.status', 2) ->whereIn('employee_leaves.date_from', $cut_off['date_range']) ->whereIn('employee_leaves.date_to', $cut_off['date_range']); // if ($request->payroll_date) { // // Apply payroll_date filter if provided // // $data = $data->whereIn('employee_leaves.payroll_date', $request->payroll_date); // } if ($request->employee_id > 0) { $data = $data->where('employee_leaves.employee_id', $request->employee_id); } if ($request->selectedCompanyId > 0) { $data = $data->where('employee_leaves.company_id', $request->selectedCompanyId); } if ($request->dealer_id > 0) { $data = $data->where('employee_leaves.dealership_id', $request->dealership_id); } $data = $data->select( DB::raw('companies.name as company'), DB::raw('dealerships.name as dealer'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"), DB::raw('leave_types.name as leavetype'), DB::raw('leave_reasons.name as reason'), DB::raw('employee_leaves.date_from'), DB::raw('employee_leaves.date_to'), DB::raw('employee_leaves.hours'), DB::raw('employee_leaves.approved_at') ) ->groupBy( DB::raw('companies.name'), DB::raw('dealerships.name'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"), DB::raw('leave_types.name'), DB::raw('leave_reasons.name'), 'employee_leaves.date_from', 'employee_leaves.date_to', 'employee_leaves.hours', DB::raw('employee_leaves.approved_at') ) ->get(); return response()->json(['data' => $data]); }else if($request->type ==2){ $data = \App\Models\EmployeeOb:: join('employees', 'employee_obs.employee_id', '=', 'employees.employee_id') ->join('employees as approver', 'employee_obs.approver_id', '=', 'approver.employee_id') ->join('companies','employee_obs.company_id','companies.id') ->join('dealerships','employee_obs.dealer_id','dealerships.id') ->where('employee_obs.posted',0) ->where('employee_obs.status', 2) ->whereIn('employee_obs.date_from', $cut_off['date_range']) ->whereIn('employee_obs.date_to', $cut_off['date_range']); if ($request->employee_id > 0) { $data = $data->where('employee_obs.employee_id', $request->employee_id); } if ($request->selectedCompanyId > 0) { $data = $data->where('employee_obs.company_id', $request->selectedCompanyId); } if ($request->dealership_id > 0) { $data = $data->where('employee_obs.dealer_id', $request->dealership_id); } $data = $data->select( DB::raw('companies.name as company'), DB::raw('dealerships.name as dealer'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"), DB::raw('TIMESTAMPDIFF(HOUR, employee_obs.from_time, employee_obs.to_time) as hours'), 'employee_obs.date_from', 'employee_obs.date_to', 'employee_obs.description', 'employee_obs.approved_at', ) ->groupBy( DB::raw('companies.name'), DB::raw('dealerships.name'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"), DB::raw('TIMESTAMPDIFF(HOUR, employee_obs.from_time, employee_obs.to_time)'), 'employee_obs.date_from', 'employee_obs.date_to', 'employee_obs.description', 'employee_obs.approved_at', ) ->get(); return response()->json(['data' => $data]); }else if($request->type ==3){ $data = \App\Models\EmployeeOvertime:: join('employees', 'employee_overtimes.employee_id', '=', 'employees.employee_id') ->join('employees as approver', 'employee_overtimes.approver_id', '=', 'approver.employee_id') ->join('companies','employee_overtimes.company_id','companies.id') ->join('dealerships','employee_overtimes.dealer_id','dealerships.id') ->where('employee_overtimes.posted',0) ->where('employee_overtimes.status', 2); // ->whereIn('employee_overtimes.date_from', $cut_off['date_range']) // ->whereIn('employee_overtimes.date_to', $cut_off['date_range']); if ($request->employee_id > 0) { $data = $data->where('employee_overtimes.employee_id', $request->employee_id); } if ($request->selectedCompanyId > 0) { $data = $data->where('employee_overtimes.company_id', $request->selectedCompanyId); } if ($request->dealership_id > 0) { $data = $data->where('employee_overtimes.dealer_id', $request->dealership_id); } $data = $data->select( DB::raw('companies.name as company'), DB::raw('dealerships.name as dealer'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"), 'employee_overtimes.date_from', 'employee_overtimes.date_to', 'employee_overtimes.hours', 'employee_overtimes.description', 'employee_overtimes.approved_at', ) ->groupBy( DB::raw('companies.name'), DB::raw('dealerships.name'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"), 'employee_overtimes.date_from', 'employee_overtimes.date_to', 'employee_overtimes.description', 'employee_overtimes.approved_at', 'employee_overtimes.hours', ) ->get(); return response()->json(['data' => $data]); }else if($request->type ==4){ $data = \App\Models\EmployeeTimeAdjustment:: join('employees', 'employee_time_adjustments.employee_id', '=', 'employees.employee_id') ->join('employees as approver', 'employee_time_adjustments.approver_id', '=', 'approver.employee_id') ->join('companies','employee_time_adjustments.company_id','companies.id') ->join('dealerships','employee_time_adjustments.dealer_id','dealerships.id') ->where('employee_time_adjustments.posted',0) ->where('employee_time_adjustments.status', 2) ->whereIn('employee_time_adjustments.date', $cut_off['date_range']); if ($request->employee_id > 0) { $data = $data->where('employee_time_adjustments.employee_id', $request->employee_id); } if ($request->selectedCompanyId > 0) { $data = $data->where('employee_time_adjustments.company_id', $request->selectedCompanyId); } if ($request->dealership_id > 0) { $data = $data->where('employee_time_adjustments.dealer_id', $request->dealership_id); } $data = $data->select( DB::raw('companies.name as company'), DB::raw('dealerships.name as dealer'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"), 'employee_time_adjustments.date', 'employee_time_adjustments.description', 'employee_time_adjustments.approved_at', DB::raw("IF(in_out = 1, 'In', 'Out') as `In/Out`") ) ->groupBy( DB::raw('companies.name'), DB::raw('dealerships.name'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"), 'employee_time_adjustments.date', 'employee_time_adjustments.description', 'employee_time_adjustments.approved_at', DB::raw("IF(in_out = 1, 'In', 'Out')") ) ->get(); return response()->json(['data' => $data]); }else if($request->type == 5){ $data = \App\Models\EmployeeChangeSchedule:: join('employees', 'employee_change_schedules.employee_id', '=', 'employees.employee_id') ->join('employees as approver', 'employee_change_schedules.approver_id', '=', 'approver.employee_id') ->join('companies','employee_change_schedules.company_id','companies.id') ->join('dealerships','employee_change_schedules.dealer_id','dealerships.id') ->where('employee_change_schedules.posted',0) ->where('employee_change_schedules.status', 2) ->whereIn('employee_change_schedules.date', $cut_off['date_range']); // ->whereIn('employee_overtimes.date_to', $cut_off['date_range']); if ($request->employee_id > 0) { $data = $data->where('employee_change_schedules.employee_id', $request->employee_id); } if ($request->selectedCompanyId > 0) { $data = $data->where('employee_change_schedules.company_id', $request->selectedCompanyId); } if ($request->dealership_id > 0) { $data = $data->where('employee_change_schedules.dealer_id', $request->dealership_id); } $data = $data->select( DB::raw('companies.name as company'), DB::raw('dealerships.name as dealer'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"), DB::raw('(TIMESTAMPDIFF(HOUR, employee_change_schedules.from_time, employee_change_schedules.to_time) +1) as hours'), 'employee_change_schedules.date', 'employee_change_schedules.description', 'employee_change_schedules.approved_at', ) ->groupBy( DB::raw('companies.name'), DB::raw('dealerships.name'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"), 'employee_change_schedules.date', 'employee_change_schedules.description', 'employee_change_schedules.approved_at', DB::raw('(TIMESTAMPDIFF(HOUR, employee_change_schedules.from_time, employee_change_schedules.to_time) +1)') ) ->get(); return response()->json(['data' => $data]); }else if($request->type == 6){ $data = \App\Models\EmployeeOffset:: join('employees', 'employee_offsets.employee_id', '=', 'employees.employee_id') ->join('employees as approver', 'employee_offsets.approver_id', '=', 'approver.employee_id') ->join('companies','employee_offsets.company_id','companies.id') ->join('dealerships','employee_offsets.dealer_id','dealerships.id') ->where('employee_offsets.posted',0) ->where('employee_offsets.status', 2); // ->whereIn('employee_offsets.date_from', $cut_off['date_range']) // ->whereIn('employee_offsets.date_to', $cut_off['date_range']); if ($request->employee_id > 0) { $data = $data->where('employee_offsets.employee_id', $request->employee_id); } if ($request->selectedCompanyId > 0) { $data = $data->where('employee_offsets.company_id', $request->selectedCompanyId); } if ($request->dealership_id > 0) { $data = $data->where('employee_offsets.dealership_id', $request->dealership_id); } $data = $data->select( DB::raw('companies.name as company'), DB::raw('dealerships.name as dealer'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname) as name"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname) as approver_name"), 'employee_offsets.date_from', 'employee_offsets.date_to', 'employee_offsets.hours', 'employee_offsets.description', 'employee_offsets.approved_at', ) ->groupBy( DB::raw('companies.name'), DB::raw('dealerships.name'), DB::raw('employees.employee_id'), DB::raw("CONCAT(employees.firstname, ' ', employees.lastname)"), DB::raw("CONCAT(approver.firstname, ' ', approver.lastname)"), 'employee_offsets.date_from', 'employee_offsets.date_to', 'employee_offsets.description', 'employee_offsets.approved_at', 'employee_offsets.hours', ) ->get(); return response()->json(['data' => $data]); } } }

| ver. 1.4 | . | PHP 8.1.32 | Generation time: 0.02 | proxy | phpinfo | Settings